{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Class04"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = '/Users/ml/Google Drive/af/teaching/database/data/'\n",
    "ds_data = pd.read_excel(file_path+'datastream_data.xlsx',sheet_name='asset')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Code</th>\n",
       "      <th>US0378331005(WC02999)</th>\n",
       "      <th>US0231351067(WC02999)</th>\n",
       "      <th>US5949181045(WC02999)</th>\n",
       "      <th>US01609W1027(WC02999)</th>\n",
       "      <th>US30303M1027(WC02999)</th>\n",
       "      <th>US30231G1022(WC02999)</th>\n",
       "      <th>US46625H1005(WC02999)</th>\n",
       "      <th>US02079K3059(WC02999)</th>\n",
       "      <th>US4781601046(WC02999)</th>\n",
       "      <th>US0605051046(WC02999)</th>\n",
       "      <th>US9497461015(WC02999)</th>\n",
       "      <th>US9311421039(WC02999)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000</td>\n",
       "      <td>6803000</td>\n",
       "      <td>2135169</td>\n",
       "      <td>52150000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>149000000</td>\n",
       "      <td>715348000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31302000</td>\n",
       "      <td>642191000</td>\n",
       "      <td>272426000</td>\n",
       "      <td>78130000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001</td>\n",
       "      <td>6021000</td>\n",
       "      <td>1637547</td>\n",
       "      <td>59257000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>143174000</td>\n",
       "      <td>693575000</td>\n",
       "      <td>84457.0</td>\n",
       "      <td>38230000</td>\n",
       "      <td>621764000</td>\n",
       "      <td>307569000</td>\n",
       "      <td>83451000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2002</td>\n",
       "      <td>6228000</td>\n",
       "      <td>1990449</td>\n",
       "      <td>67646000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>152644000</td>\n",
       "      <td>758800000</td>\n",
       "      <td>291192.0</td>\n",
       "      <td>40345000</td>\n",
       "      <td>660458000</td>\n",
       "      <td>349259000</td>\n",
       "      <td>94685000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2003</td>\n",
       "      <td>6755000</td>\n",
       "      <td>2162033</td>\n",
       "      <td>79571000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>174278000</td>\n",
       "      <td>770912000</td>\n",
       "      <td>2306507.0</td>\n",
       "      <td>47589000</td>\n",
       "      <td>736445000</td>\n",
       "      <td>387798000</td>\n",
       "      <td>104912000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004</td>\n",
       "      <td>7964000</td>\n",
       "      <td>2966751</td>\n",
       "      <td>90560000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>193850000</td>\n",
       "      <td>1157248000</td>\n",
       "      <td>3301761.0</td>\n",
       "      <td>52777000</td>\n",
       "      <td>1110457000</td>\n",
       "      <td>427849000</td>\n",
       "      <td>120223000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Code  US0378331005(WC02999)  US0231351067(WC02999)  US5949181045(WC02999)  \\\n",
       "0  2000                6803000                2135169               52150000   \n",
       "1  2001                6021000                1637547               59257000   \n",
       "2  2002                6228000                1990449               67646000   \n",
       "3  2003                6755000                2162033               79571000   \n",
       "4  2004                7964000                2966751               90560000   \n",
       "\n",
       "   US01609W1027(WC02999)  US30303M1027(WC02999)  US30231G1022(WC02999)  \\\n",
       "0                    NaN                    NaN              149000000   \n",
       "1                    NaN                    NaN              143174000   \n",
       "2                    NaN                    NaN              152644000   \n",
       "3                    NaN                    NaN              174278000   \n",
       "4                    NaN                    NaN              193850000   \n",
       "\n",
       "   US46625H1005(WC02999)  US02079K3059(WC02999)  US4781601046(WC02999)  \\\n",
       "0              715348000                    NaN               31302000   \n",
       "1              693575000                84457.0               38230000   \n",
       "2              758800000               291192.0               40345000   \n",
       "3              770912000              2306507.0               47589000   \n",
       "4             1157248000              3301761.0               52777000   \n",
       "\n",
       "   US0605051046(WC02999)  US9497461015(WC02999)  US9311421039(WC02999)  \n",
       "0              642191000              272426000               78130000  \n",
       "1              621764000              307569000               83451000  \n",
       "2              660458000              349259000               94685000  \n",
       "3              736445000              387798000              104912000  \n",
       "4             1110457000              427849000              120223000  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transpose data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Code</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000</td>\n",
       "      <td>US0378331005(WC02999)</td>\n",
       "      <td>6803000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001</td>\n",
       "      <td>US0378331005(WC02999)</td>\n",
       "      <td>6021000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2002</td>\n",
       "      <td>US0378331005(WC02999)</td>\n",
       "      <td>6228000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2003</td>\n",
       "      <td>US0378331005(WC02999)</td>\n",
       "      <td>6755000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004</td>\n",
       "      <td>US0378331005(WC02999)</td>\n",
       "      <td>7964000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Code               variable      value\n",
       "0  2000  US0378331005(WC02999)  6803000.0\n",
       "1  2001  US0378331005(WC02999)  6021000.0\n",
       "2  2002  US0378331005(WC02999)  6228000.0\n",
       "3  2003  US0378331005(WC02999)  6755000.0\n",
       "4  2004  US0378331005(WC02999)  7964000.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_data_1 = pd.melt(ds_data,id_vars='Code',value_vars=ds_data.columns[1:])\n",
    "ds_data_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>isin</th>\n",
       "      <th>year</th>\n",
       "      <th>asset</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2001</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2002</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2003</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2004</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2005</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2006</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2007</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2008</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2009</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2010</td>\n",
       "      <td>6110076.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2011</td>\n",
       "      <td>5776641.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2012</td>\n",
       "      <td>7619217.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2013</td>\n",
       "      <td>10398346.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2014</td>\n",
       "      <td>18018900.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            isin  year       asset\n",
       "0   US01609W1027  2000         NaN\n",
       "1   US01609W1027  2001         NaN\n",
       "2   US01609W1027  2002         NaN\n",
       "3   US01609W1027  2003         NaN\n",
       "4   US01609W1027  2004         NaN\n",
       "5   US01609W1027  2005         NaN\n",
       "6   US01609W1027  2006         NaN\n",
       "7   US01609W1027  2007         NaN\n",
       "8   US01609W1027  2008         NaN\n",
       "9   US01609W1027  2009         NaN\n",
       "10  US01609W1027  2010   6110076.0\n",
       "11  US01609W1027  2011   5776641.0\n",
       "12  US01609W1027  2012   7619217.0\n",
       "13  US01609W1027  2013  10398346.0\n",
       "14  US01609W1027  2014  18018900.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_data_2 = ds_data_1.copy()\n",
    "ds_data_2.columns = ['year','isin','asset']\n",
    "ds_data_2['isin'] = ds_data_2['isin'].str[:12]\n",
    "ds_data_2 = ds_data_2[['isin','year','asset']]\n",
    "ds_data_2 = ds_data_2.sort_values(['isin','year']).reset_index(drop=True)\n",
    "ds_data_2.head(15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge with fiscal year end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Code</th>\n",
       "      <th>US0378331005(WC05350)</th>\n",
       "      <th>US0231351067(WC05350)</th>\n",
       "      <th>US5949181045(WC05350)</th>\n",
       "      <th>US01609W1027(WC05350)</th>\n",
       "      <th>US30303M1027(WC05350)</th>\n",
       "      <th>US30231G1022(WC05350)</th>\n",
       "      <th>US46625H1005(WC05350)</th>\n",
       "      <th>US02079K3059(WC05350)</th>\n",
       "      <th>US4781601046(WC05350)</th>\n",
       "      <th>US0605051046(WC05350)</th>\n",
       "      <th>US9497461015(WC05350)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000</td>\n",
       "      <td>9/30/2000</td>\n",
       "      <td>12/31/2000</td>\n",
       "      <td>6/30/2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12/31/2000</td>\n",
       "      <td>12/31/2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12/31/2000</td>\n",
       "      <td>12/31/2000</td>\n",
       "      <td>12/31/2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001</td>\n",
       "      <td>9/29/2001</td>\n",
       "      <td>12/31/2001</td>\n",
       "      <td>6/30/2001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12/31/2001</td>\n",
       "      <td>12/31/2001</td>\n",
       "      <td>12/31/2001</td>\n",
       "      <td>12/30/2001</td>\n",
       "      <td>12/31/2001</td>\n",
       "      <td>12/31/2001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2002</td>\n",
       "      <td>9/28/2002</td>\n",
       "      <td>12/31/2002</td>\n",
       "      <td>6/30/2002</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12/31/2002</td>\n",
       "      <td>12/31/2002</td>\n",
       "      <td>12/31/2002</td>\n",
       "      <td>12/29/2002</td>\n",
       "      <td>12/31/2002</td>\n",
       "      <td>12/31/2002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2003</td>\n",
       "      <td>9/27/2003</td>\n",
       "      <td>12/31/2003</td>\n",
       "      <td>6/30/2003</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12/31/2003</td>\n",
       "      <td>12/31/2003</td>\n",
       "      <td>12/31/2003</td>\n",
       "      <td>12/28/2003</td>\n",
       "      <td>12/31/2003</td>\n",
       "      <td>12/31/2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004</td>\n",
       "      <td>9/25/2004</td>\n",
       "      <td>12/31/2004</td>\n",
       "      <td>6/30/2004</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12/31/2004</td>\n",
       "      <td>12/31/2004</td>\n",
       "      <td>12/31/2004</td>\n",
       "      <td>2005-02-01 00:00:00</td>\n",
       "      <td>12/31/2004</td>\n",
       "      <td>12/31/2004</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Code US0378331005(WC05350) US0231351067(WC05350) US5949181045(WC05350)  \\\n",
       "0  2000             9/30/2000            12/31/2000             6/30/2000   \n",
       "1  2001             9/29/2001            12/31/2001             6/30/2001   \n",
       "2  2002             9/28/2002            12/31/2002             6/30/2002   \n",
       "3  2003             9/27/2003            12/31/2003             6/30/2003   \n",
       "4  2004             9/25/2004            12/31/2004             6/30/2004   \n",
       "\n",
       "  US01609W1027(WC05350) US30303M1027(WC05350) US30231G1022(WC05350)  \\\n",
       "0                   NaN                   NaN            12/31/2000   \n",
       "1                   NaN                   NaN            12/31/2001   \n",
       "2                   NaN                   NaN            12/31/2002   \n",
       "3                   NaN                   NaN            12/31/2003   \n",
       "4                   NaN                   NaN            12/31/2004   \n",
       "\n",
       "  US46625H1005(WC05350) US02079K3059(WC05350) US4781601046(WC05350)  \\\n",
       "0            12/31/2000                   NaN            12/31/2000   \n",
       "1            12/31/2001            12/31/2001            12/30/2001   \n",
       "2            12/31/2002            12/31/2002            12/29/2002   \n",
       "3            12/31/2003            12/31/2003            12/28/2003   \n",
       "4            12/31/2004            12/31/2004   2005-02-01 00:00:00   \n",
       "\n",
       "  US0605051046(WC05350) US9497461015(WC05350)  \n",
       "0            12/31/2000            12/31/2000  \n",
       "1            12/31/2001            12/31/2001  \n",
       "2            12/31/2002            12/31/2002  \n",
       "3            12/31/2003            12/31/2003  \n",
       "4            12/31/2004            12/31/2004  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fyear = pd.read_excel(file_path+'datastream_data.xlsx',sheet_name='fyend')\n",
    "fyear.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>isin</th>\n",
       "      <th>year</th>\n",
       "      <th>fyear</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2001</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2002</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2003</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2004</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2005</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2006</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2007</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2008</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2009</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2010</td>\n",
       "      <td>3/31/2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2011</td>\n",
       "      <td>3/31/2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2012</td>\n",
       "      <td>3/31/2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2013</td>\n",
       "      <td>3/31/2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2014</td>\n",
       "      <td>3/31/2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            isin  year      fyear\n",
       "0   US01609W1027  2000        NaN\n",
       "1   US01609W1027  2001        NaN\n",
       "2   US01609W1027  2002        NaN\n",
       "3   US01609W1027  2003        NaN\n",
       "4   US01609W1027  2004        NaN\n",
       "5   US01609W1027  2005        NaN\n",
       "6   US01609W1027  2006        NaN\n",
       "7   US01609W1027  2007        NaN\n",
       "8   US01609W1027  2008        NaN\n",
       "9   US01609W1027  2009        NaN\n",
       "10  US01609W1027  2010  3/31/2010\n",
       "11  US01609W1027  2011  3/31/2011\n",
       "12  US01609W1027  2012  3/31/2012\n",
       "13  US01609W1027  2013  3/31/2013\n",
       "14  US01609W1027  2014  3/31/2014"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fyear_1 = pd.melt(fyear,id_vars='Code',value_vars=fyear.columns[1:])\n",
    "fyear_1.columns = ['year','isin','fyear']\n",
    "fyear_1['isin'] = fyear_1['isin'].str[:12]\n",
    "fyear_1 = fyear_1[['isin','year','fyear']]\n",
    "fyear_1 = fyear_1.sort_values(['isin','year']).reset_index(drop=True)\n",
    "fyear_1.head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>isin</th>\n",
       "      <th>year</th>\n",
       "      <th>asset</th>\n",
       "      <th>fyear</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2002</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2003</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2004</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2005</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2006</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2008</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2009</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2010</td>\n",
       "      <td>6110076.0</td>\n",
       "      <td>3/31/2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2011</td>\n",
       "      <td>5776641.0</td>\n",
       "      <td>3/31/2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2012</td>\n",
       "      <td>7619217.0</td>\n",
       "      <td>3/31/2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2013</td>\n",
       "      <td>10398346.0</td>\n",
       "      <td>3/31/2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>US01609W1027</td>\n",
       "      <td>2014</td>\n",
       "      <td>18018900.0</td>\n",
       "      <td>3/31/2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            isin  year       asset      fyear\n",
       "0   US01609W1027  2000         NaN        NaN\n",
       "1   US01609W1027  2001         NaN        NaN\n",
       "2   US01609W1027  2002         NaN        NaN\n",
       "3   US01609W1027  2003         NaN        NaN\n",
       "4   US01609W1027  2004         NaN        NaN\n",
       "5   US01609W1027  2005         NaN        NaN\n",
       "6   US01609W1027  2006         NaN        NaN\n",
       "7   US01609W1027  2007         NaN        NaN\n",
       "8   US01609W1027  2008         NaN        NaN\n",
       "9   US01609W1027  2009         NaN        NaN\n",
       "10  US01609W1027  2010   6110076.0  3/31/2010\n",
       "11  US01609W1027  2011   5776641.0  3/31/2011\n",
       "12  US01609W1027  2012   7619217.0  3/31/2012\n",
       "13  US01609W1027  2013  10398346.0  3/31/2013\n",
       "14  US01609W1027  2014  18018900.0  3/31/2014"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged = ds_data_2.merge(fyear_1,how='inner',on=['isin','year'])\n",
    "merged = merged.sort_values(['isin','year']).reset_index(drop=True)\n",
    "merged.head(15)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
